package zy.dao.shop.price.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.price.PriceDAO;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.product.T_Base_Product_Shop_Price;
import zy.entity.shop.price.T_Shop_Price;
import zy.entity.shop.price.T_Shop_PriceList;
import zy.entity.shop.price.T_Shop_Price_Shop;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class PriceDAOImpl extends BaseDaoImpl implements PriceDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object sp_state = params.get("sp_state");
		Object sp_manager = params.get("sp_manager");
		Object sp_number = params.get("sp_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_shop_price t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(sp_state)) {
			sql.append(" AND sp_state = :sp_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND t.sp_sysdate >= '" + begindate + " 00:00:00 ' ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND t.sp_sysdate <= '" + enddate + " 23:59:59 ' ");
		}
		if (StringUtil.isNotEmpty(sp_manager)) {
			sql.append(" AND sp_manager = :sp_manager ");
		}
		if (StringUtil.isNotEmpty(sp_number)) {
			sql.append(" AND INSTR(sp_number,:sp_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Shop_Price> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object sp_state = params.get("sp_state");
		Object sp_manager = params.get("sp_manager");
		Object sp_number = params.get("sp_number");;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT t.sp_id,t.sp_number,t.sp_maker,sp_makerdate,t.sp_sysdate,t.sp_manager,t.sp_is_sellprice,t.sp_is_vipprice,t.sp_is_sortprice,t.sp_is_costprice,t.sp_state,");
		sql.append(" t.sp_shop_type,t.sp_us_id,t.companyid,GROUP_CONCAT(sp.sp_name) AS sp_sp_name ");
		sql.append(" FROM t_shop_price t");
		sql.append(" join t_shop_price_shop sps on sps.sps_number = t.sp_number and sps.companyid = t.companyid ");
		sql.append(" join t_base_shop sp on sp.sp_code = sps.sps_shop_code AND sp.companyid = sps.companyid ");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(sp_state)) {
			sql.append(" AND t.sp_state = :sp_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND t.sp_sysdate >= '" + begindate + " 00:00:00 ' ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND t.sp_sysdate <= '" + enddate + " 23:59:59 ' ");
		}
		if (StringUtil.isNotEmpty(sp_manager)) {
			sql.append(" AND t.sp_manager = :sp_manager ");
		}
		if (StringUtil.isNotEmpty(sp_number)) {
			sql.append(" AND INSTR(t.sp_number,:sp_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid ");
		sql.append(" group by t.sp_number ");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY t.sp_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_Price.class));	
	}

	@Override
	public List<T_Shop_PriceList> price_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT spl_id,spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,spl_new_sortprice,");
		sql.append(" spl_old_costprice,spl_new_costprice,spl_shop_code,pd.pd_name,pd.pd_no,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp.sp_code = spl_shop_code AND sp.companyid = t.companyid LIMIT 1) AS sp_name");
		sql.append(" FROM t_shop_pricelist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.spl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND spl_number = :sp_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY spl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_PriceList.class));
	}

	@Override
	public List<T_Shop_PriceList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT spl_id,spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,spl_new_sortprice,");
		sql.append(" spl_old_costprice,spl_new_costprice,spl_shop_code,pd.pd_name,pd.pd_no,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp.sp_code = spl_shop_code AND sp.companyid = t.companyid LIMIT 1) AS sp_name");
		sql.append(" FROM t_shop_pricelist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.spl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND spl_us_id = :spl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY spl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_PriceList.class));
	}

	@Override
	public List<T_Shop_PriceList> temp_list_forsave(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT spl_id,spl_number,spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,");
		sql.append(" spl_old_sortprice,spl_new_sortprice,spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,t.companyid");
		sql.append(" FROM t_shop_pricelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND spl_us_id = :spl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("spl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_PriceList.class));
	}

	@Override
	public void save_temp_list(Map<String, Object> params) {
		Integer spl_us_id = (Integer)params.get("spl_us_id");
		String pd_code = (String)params.get("pd_code");
		String sp_shop_code = (String)params.get("sp_shop_code");
		Double discount = (Double)params.get("discount");
		Integer sp_shop_type = (Integer)params.get("sp_shop_type");
		Integer companyid = (Integer)params.get("companyid");
		
		List<String> shopCodeList = new ArrayList<String>(Arrays.asList(sp_shop_code.split(",")));
		List<String> pdCodeList = new ArrayList<String>(Arrays.asList(pd_code.split(",")));
		sp_shop_code = "'"+sp_shop_code.replace(",", "','")+"'";			
		pd_code = "'"+pd_code.replace(",", "','")+"'";
		
		Map<String, T_Base_Product> productMap = new HashMap<String,T_Base_Product>();
		List<T_Base_Product> productList = new ArrayList<T_Base_Product>();
		T_Base_Product product = null;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_code,pd_no,pd_point,pd_sell_price,pd_vip_price,pd_cost_price,pd_sort_price ");
		sql.append(" FROM t_base_product t");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd_code IN ("+pd_code+")");
		sql.append(" AND pd_state!= '2'");
		sql.append(" AND t.companyid = :companyid");
		productList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Product.class));
		
		if(productList != null && productList.size()>0){
			for(int i=0;i<productList.size();i++){
				productMap.put(productList.get(i).getPd_code(), productList.get(i));
			}
		}
		
		//根据货号，店铺编号，商家编号 得到对应的 分店原始价格
		Map<String, T_Base_Product_Shop_Price> shopPriceMap = new HashMap<String,T_Base_Product_Shop_Price>();
		List<T_Base_Product_Shop_Price> shopPriceList = new ArrayList<T_Base_Product_Shop_Price>();
		sql.setLength(0);
		sql.append(" SELECT pd_code,pd_no,pdp_shop_code,");
		sql.append(" IFNULL(pdp_cost_price,pd_cost_price) AS pdp_cost_price,");
		sql.append(" IFNULL(pdp_sell_price, pd_sell_price) AS pdp_sell_price,");
		sql.append(" IFNULL(pdp_vip_price,pd_vip_price) AS pdp_vip_price,");
		sql.append(" IFNULL(pdp_sort_price, pd_sort_price) AS pdp_sort_price,");
		sql.append(" IFNULL(pdp_pd_point, pd_point) AS pdp_pd_point");
		sql.append(" FROM");
		sql.append(" t_base_product t");
		sql.append(" LEFT JOIN t_base_product_shop_price pdp ON pdp.pdp_pd_code = t.pd_code AND pdp.companyid = t.companyid AND pdp.pdp_shop_code IN (").append(sp_shop_code).append(")");
		sql.append(" WHERE");
		sql.append(" 1 = 1");
		sql.append(" AND t.pd_code in (").append(pd_code).append(")");
		sql.append(" AND t.pd_state !='2'");
		sql.append(" AND t.companyid = :companyid");
		shopPriceList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Product_Shop_Price.class));
		
		T_Base_Product_Shop_Price shop_price = null;
		if(shopPriceList != null && shopPriceList.size()>0){
			for(int i=0;i<shopPriceList.size();i++){
				shop_price = shopPriceList.get(i);
				if(shop_price.getPdp_shop_code() !=null && !"".equals(shop_price.getPdp_shop_code())){
					shopPriceMap.put(shop_price.getPdp_shop_code()+"_"+shop_price.getPd_code(), shop_price);
				}
			}
		}
		
		//查询商品调价临时表，用来判断临时表中是否存在要选择的货号信息
		List<T_Shop_PriceList> tempList = new ArrayList<T_Shop_PriceList>();
		sql.setLength(0);
		sql.append(" SELECT spl_shop_code,spl_pd_code FROM t_shop_pricelist_temp ");
		sql.append(" WHERE ");
		sql.append(" spl_us_id =").append(spl_us_id);
		tempList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_PriceList.class));
		Set<String> tempSet = new HashSet<String>();
		if(tempList != null && tempList.size()>0){
			for(int i=0;i<tempList.size();i++){
				tempSet.add(tempList.get(i).getSpl_shop_code()+"_"+tempList.get(i).getSpl_pd_code());
			}
		}
		
		//把选定的货号，对应的分店价格，放到resultList中，如果选定的货号没有分店价格，就选取商品资料中该货号对应的价格
		List<T_Shop_PriceList> resultList = new ArrayList<T_Shop_PriceList>();
		T_Shop_PriceList model = null;
		for (String shopCode : shopCodeList) {
			for (String pdCode : pdCodeList) {
				if(tempSet.contains(shopCode+"_"+pdCode)){
					continue;
				}
				if(shopPriceMap.containsKey(shopCode+"_"+pdCode)){
					shop_price = shopPriceMap.get(shopCode+"_"+pdCode);
					model = new T_Shop_PriceList();
					model.setSpl_old_sellprice(shop_price.getPdp_sell_price());
					model.setSpl_new_sellprice(shop_price.getPdp_sell_price()*discount);
					model.setSpl_old_vipprice(shop_price.getPdp_vip_price());
					model.setSpl_new_vipprice(shop_price.getPdp_vip_price());
					model.setSpl_old_sortprice(shop_price.getPdp_sort_price());
					model.setSpl_new_sortprice(shop_price.getPdp_sort_price());
					model.setSpl_old_costprice(shop_price.getPdp_cost_price());
					model.setSpl_new_costprice(shop_price.getPdp_cost_price());
					model.setSpl_pd_point(shop_price.getPdp_pd_point());
					model.setSpl_pd_code(pdCode);
					model.setSpl_shop_code(shopCode);
					model.setSpl_us_id(spl_us_id);
					model.setCompanyid(companyid);
				} else {
					product = productMap.get(pdCode);
					model = new T_Shop_PriceList();
					model.setSpl_old_sellprice(product.getPd_sell_price());
					model.setSpl_new_sellprice(product.getPd_sell_price()*discount);
					model.setSpl_old_vipprice(product.getPd_vip_price());
					model.setSpl_new_vipprice(product.getPd_vip_price());
					model.setSpl_old_sortprice(product.getPd_sort_price());
					model.setSpl_new_sortprice(product.getPd_sort_price());
					if(CommonUtil.FOUR.equals(sp_shop_type) || CommonUtil.FIVE.equals(sp_shop_type)){//加盟店或者合伙店将总部的配送价保存在分店的成本价中
						model.setSpl_old_costprice(product.getPd_sort_price());
						model.setSpl_new_costprice(product.getPd_sort_price());
					}else{
						model.setSpl_old_costprice(product.getPd_cost_price());
						model.setSpl_new_costprice(product.getPd_cost_price());
					}
					model.setSpl_pd_point(product.getPd_point());
					model.setSpl_pd_code(pdCode);
					model.setSpl_shop_code(shopCode);
					model.setSpl_us_id(spl_us_id);
					model.setCompanyid(companyid);
				}
				resultList.add(model);
			}
		}
		
		//把选定的货号，添加到商品调价临时表中
		sql.setLength(0);
		sql.append(" insert into t_shop_pricelist_temp ");
		sql.append(" (spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,");
		sql.append(" spl_new_sortprice,spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:spl_pd_code,:spl_pd_point,:spl_old_sellprice,:spl_new_sellprice,:spl_old_vipprice,:spl_new_vipprice,:spl_old_sortprice,");
		sql.append(" :spl_new_sortprice,:spl_old_costprice,:spl_new_costprice,:spl_shop_code,:spl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(resultList.toArray()));
	}

	@Override
	public String save_tempList_Enter(Map<String, Object> params) {
		Integer companyid = (Integer)params.get("companyid");
		Object pd_no = params.get("pd_no");
		Integer spl_us_id = (Integer)params.get("spl_us_id");
		String sp_shop_code = (String)params.get("sp_shop_code");
		Double discount = (Double)params.get("discount");
		Object sp_shop_type = params.get("sp_shop_type");
//		String sp_is_sellprice = (String)params.get("sp_is_sellprice");
//		String sp_is_vipprice = (String)params.get("sp_is_vipprice");
//		String sp_is_sortprice = (String)params.get("sp_is_sortprice");
//		String sp_is_costprice = (String)params.get("sp_is_costprice");
//		
//		boolean modifySellPrice = "1".equals(sp_is_sellprice);
//		boolean modifyVipPrice = "1".equals(sp_is_vipprice);
//		boolean modifySortPrice = "1".equals(sp_is_sortprice);
//		boolean modifyCostPrice = "1".equals(sp_is_costprice);
		
		String[] sp_shop_codes = sp_shop_code.split(",");
		sp_shop_code = "'"+sp_shop_code.replace(",", "','")+"'";			
		
		List<T_Shop_PriceList> shopCodeList = new ArrayList<T_Shop_PriceList>();
		Set<String> shopCodeSet = new HashSet<String>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT spl_shop_code FROM t_shop_pricelist_temp t ");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = t.spl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND pd.pd_no = :pd_no");
		sql.append(" AND t.spl_us_id = :spl_us_id");
		sql.append(" AND t.companyid =:companyid");
		shopCodeList = namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("spl_us_id", spl_us_id).addValue("companyid", companyid).addValue("pd_no", pd_no),
				new BeanPropertyRowMapper<>(T_Shop_PriceList.class));
		if(shopCodeList!=null && shopCodeList.size()>0){
			for(int i=0;i<shopCodeList.size();i++){
				shopCodeSet.add(shopCodeList.get(i).getSpl_shop_code());
			}
		}
		
		List<String> saveShopCode = new ArrayList<String>();//封装不存在该货号的店铺编号
		for(String shop_code : sp_shop_codes){
			if(!shopCodeSet.contains(shop_code)){
				saveShopCode.add(shop_code);
			}
		}
		if(saveShopCode.isEmpty()){//如果为空，说明输入的货号已经存在在，对应店铺的商品调价零售表中
			return "3";//3状态为货号在临时表已经存在
		}
		//获得原零售价，原会员价，原配送价，原成本价
		//查询货号的分店价格数据
		Map<String, T_Base_Product_Shop_Price> shopPriceMap = new HashMap<String,T_Base_Product_Shop_Price>();
		List<T_Base_Product_Shop_Price> shopPriceList = new ArrayList<T_Base_Product_Shop_Price>();
		sql.setLength(0);
		sql.append(" SELECT pd_code,pd_no,pdp_shop_code,");
		sql.append(" IFNULL(pdp_cost_price,pd_cost_price) AS pdp_cost_price,");
		sql.append(" IFNULL(pdp_sell_price, pd_sell_price) AS pdp_sell_price,");
		sql.append(" IFNULL(pdp_vip_price,pd_vip_price) AS pdp_vip_price,");
		sql.append(" IFNULL(pdp_sort_price, pd_sort_price) AS pdp_sort_price,");
		sql.append(" IFNULL(pdp_pd_point, pd_point) AS pdp_pd_point");
		sql.append(" FROM");
		sql.append(" t_base_product t");
		sql.append(" LEFT JOIN t_base_product_shop_price pdp ON pdp.pdp_pd_code = t.pd_code AND pdp.companyid = t.companyid AND pdp.pdp_shop_code IN (").append(sp_shop_code).append(")");
		sql.append(" WHERE");
		sql.append(" 1 = 1");
		sql.append(" AND t.pd_no = :pd_no");
		sql.append(" AND t.pd_state !='2'");
		sql.append(" AND t.companyid = :companyid");
		shopPriceList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Product_Shop_Price.class));
		
		T_Base_Product_Shop_Price shop_price = null;
		if(shopPriceList != null && shopPriceList.size()>0){
			for(int i=0;i<shopPriceList.size();i++){
				shop_price = shopPriceList.get(i);
				if(shop_price.getPdp_shop_code() !=null && !"".equals(shop_price.getPdp_shop_code())){
					shopPriceMap.put(shop_price.getPdp_shop_code()+"_"+shop_price.getPd_no(), shop_price);
				}
			}
		}
		
		Map<String, T_Base_Product> productMap = new HashMap<String,T_Base_Product>();
		List<T_Base_Product> productList = new ArrayList<T_Base_Product>();
		T_Base_Product product = null;
		sql.setLength(0);
		sql.append(" SELECT pd_code,pd_no,pd_point,pd_sell_price,pd_vip_price,pd_cost_price,pd_sort_price ");
		sql.append(" FROM t_base_product t");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd_no = :pd_no");
		sql.append(" AND pd_state!= '2'");
		sql.append(" AND t.companyid = :companyid");
		productList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Product.class));
		
		if(productList != null && productList.size()>0){
			for(int i=0;i<productList.size();i++){
				productMap.put(productList.get(i).getPd_no(), productList.get(i));
			}
		}
		
		//把选定的货号，对应的分店价格，放到resultList中，如果选定的货号没有分店价格，就选取商品资料中该货号对应的价格
		List<T_Shop_PriceList> resultList = new ArrayList<T_Shop_PriceList>();
		T_Shop_PriceList model = null;
		
		for(String shopCode : saveShopCode){
			if (pd_no != null && !"".equals(pd_no)) {
				model = new T_Shop_PriceList();
				if(shopPriceMap.containsKey(shopCode + "_" + pd_no)){
					shop_price = shopPriceMap.get(shopCode + "_" + pd_no);
					model = new T_Shop_PriceList();
					model.setSpl_old_sellprice(shop_price.getPdp_sell_price());
//					if(modifySellPrice){
						model.setSpl_new_sellprice(shop_price.getPdp_sell_price()*discount);
//					}
					model.setSpl_old_vipprice(shop_price.getPdp_vip_price());
//					if(modifyVipPrice){
						model.setSpl_new_vipprice(shop_price.getPdp_vip_price());
//					}
					model.setSpl_old_sortprice(shop_price.getPdp_sort_price());
//					if(modifySortPrice){
						model.setSpl_new_sortprice(shop_price.getPdp_sort_price());
//					}
					model.setSpl_old_costprice(shop_price.getPdp_cost_price());
//					if(modifyCostPrice){
						model.setSpl_new_costprice(shop_price.getPdp_cost_price());
//					}
					model.setSpl_pd_code(shop_price.getPd_code());
					model.setSpl_pd_point(shop_price.getPdp_pd_point());
					model.setSpl_shop_code(shopCode);
					model.setSpl_us_id(spl_us_id);
					model.setCompanyid(companyid);
				} else {
					product = productMap.get(pd_no);
					model = new T_Shop_PriceList();
					model.setSpl_old_sellprice(product.getPd_sell_price());
//					if(modifySellPrice){
						model.setSpl_new_sellprice(product.getPd_sell_price()*discount);
//					}
					model.setSpl_old_vipprice(product.getPd_vip_price());
//					if(modifyVipPrice){
						model.setSpl_new_vipprice(product.getPd_vip_price());
//					}
					model.setSpl_old_sortprice(product.getPd_sort_price());
//					if(modifySortPrice){
						model.setSpl_new_sortprice(product.getPd_sort_price());
//					}
					if(CommonUtil.FOUR.equals(sp_shop_type) || CommonUtil.FIVE.equals(sp_shop_type)){//加盟店或者合伙店将总部的配送价保存在分店的成本价中
						model.setSpl_old_costprice(product.getPd_sort_price());
//						if(modifyCostPrice){
							model.setSpl_new_costprice(product.getPd_sort_price());
//						}
					}else{
						model.setSpl_old_costprice(product.getPd_cost_price());
//						if(modifyCostPrice){
							model.setSpl_new_costprice(product.getPd_cost_price());
//						}
					}
					model.setSpl_pd_code(product.getPd_code());
					model.setSpl_pd_point(product.getPd_point());
					model.setSpl_shop_code(shopCode);
					model.setSpl_us_id(spl_us_id);
					model.setCompanyid(companyid);
				}
				resultList.add(model);
			}
		}
		
		//把选定的货号，添加到商品调价临时表中
		sql.setLength(0);
		sql.append(" insert into t_shop_pricelist_temp ");
		sql.append(" (spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,");
		sql.append(" spl_new_sortprice,spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:spl_pd_code,:spl_pd_point,:spl_old_sellprice,:spl_new_sellprice,:spl_old_vipprice,:spl_new_vipprice,:spl_old_sortprice,");
		sql.append(" :spl_new_sortprice,:spl_old_costprice,:spl_new_costprice,:spl_shop_code,:spl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(resultList.toArray()));
		return "1";
	}

	@Override
	public void shop_change_templist(Map<String, Object> params) {
		Integer companyid = (Integer)params.get("companyid");
		Integer spl_us_id = (Integer)params.get("spl_us_id");
		String shopCodeBefore = (String)params.get("shopCodeBefore");
		String sp_shop_code = (String)params.get("sp_shop_code");
		Double discount = (Double)params.get("discount");
		Integer sp_shop_type = (Integer)params.get("sp_shop_type");
		List<String> delShopCode = (List<String>)params.get("delShopCode");
		List<String> addShopCode = (List<String>)params.get("addShopCode");
		
		//查询临时表中已有的商品code
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT");
		sql.append(" DISTINCT spl_pd_code FROM t_shop_pricelist_temp t ");
		sql.append(" WHERE 1 = 1 ");
		sql.append(" AND t.spl_us_id = :spl_us_id");
		sql.append(" AND t.companyid = :companyid");
		List<T_Shop_PriceList> priceLists = null;
		priceLists = namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("spl_us_id", spl_us_id).addValue("companyid", companyid),new BeanPropertyRowMapper<>(T_Shop_PriceList.class));
		StringBuffer pdCodes = new StringBuffer();
		if(priceLists != null && priceLists.size()>0){
			for(int i=0;i<priceLists.size();i++){
				pdCodes.append(priceLists.get(i).getSpl_pd_code()).append(",");
			}
		}else {
			return;
		}
		//如果原店铺编号为空 删除临时表中的数据
		if(shopCodeBefore == null || "".equals(shopCodeBefore)){
			sql.setLength(0);
			sql.append(" DELETE FROM t_shop_pricelist_temp WHERE 1 = 1 AND spl_us_id = :spl_us_id AND companyid = :companyid");
			namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("spl_us_id", spl_us_id).addValue("companyid", companyid));
		}
		
		//删除(delShopCode)
		List<T_Shop_PriceList> delShoPriceList = new ArrayList<T_Shop_PriceList>();
		T_Shop_PriceList delShop_PriceList = null;
		if(delShopCode != null && delShopCode.size()>0){
			for(int i=0;i<delShopCode.size();i++){
				delShop_PriceList = new T_Shop_PriceList();
				delShop_PriceList.setCompanyid(companyid);
				delShop_PriceList.setSpl_us_id(spl_us_id);
				delShop_PriceList.setSpl_shop_code(delShopCode.get(i));
				delShoPriceList.add(delShop_PriceList);
			}
			sql.setLength(0);
			sql.append(" DELETE FROM t_shop_pricelist_temp WHERE spl_shop_code = :spl_shop_code AND spl_us_id = :spl_us_id AND companyid = :companyid ");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(delShoPriceList.toArray()));
		}
		
		//增加店铺的货号分店价格到临时表(addShopCode)
		if(addShopCode != null && addShopCode.size() > 0){
			String pdcodes = "";
			pdcodes = pdCodes.deleteCharAt(pdCodes.length()-1).toString();
			List<String> pdCodeList = new ArrayList<String>(Arrays.asList(pdcodes.split(",")));
			pdcodes = "'"+pdcodes.replace(",", "','")+"'";
			
			//查询商品资料中的价格数据
			Map<String, T_Base_Product> productMap = new HashMap<String,T_Base_Product>();
			List<T_Base_Product> productList = new ArrayList<T_Base_Product>();
			T_Base_Product product = null;
			sql.setLength(0);
			sql.append(" SELECT pd_code,pd_no,pd_point,pd_sell_price,pd_vip_price,pd_cost_price,pd_sort_price ");
			sql.append(" FROM t_base_product t");
			sql.append(" WHERE 1=1");
			sql.append(" AND pd_code IN ("+pdcodes+")");
			sql.append(" AND pd_state!= '2'");
			sql.append(" AND t.companyid = :companyid");
			productList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Product.class));
			
			if(productList != null && productList.size()>0){
				for(int i=0;i<productList.size();i++){
					productMap.put(productList.get(i).getPd_code(), productList.get(i));
				}
			}
			
			//根据货号，店铺编号，商家编号 得到对应的 分店原始价格
			StringBuffer shopCodes = new StringBuffer("");
			String shopcodes = "";
			for(String shopcode : addShopCode){
				shopCodes.append(shopcode).append(",");
			}
			shopcodes = shopCodes.deleteCharAt(shopCodes.length()-1).toString();
			shopcodes = "'"+shopcodes.replace(",", "','")+"'";
			
			Map<String, T_Base_Product_Shop_Price> shopPriceMap = new HashMap<String,T_Base_Product_Shop_Price>();
			List<T_Base_Product_Shop_Price> shopPriceList = new ArrayList<T_Base_Product_Shop_Price>();
			sql.setLength(0);
			sql.append(" SELECT pd_code,pd_no,pdp_shop_code,");
			sql.append(" IFNULL(pdp_cost_price,pd_cost_price) AS pdp_cost_price,");
			sql.append(" IFNULL(pdp_sell_price, pd_sell_price) AS pdp_sell_price,");
			sql.append(" IFNULL(pdp_vip_price,pd_vip_price) AS pdp_vip_price,");
			sql.append(" IFNULL(pdp_sort_price, pd_sort_price) AS pdp_sort_price,");
			sql.append(" IFNULL(pdp_pd_point, pd_point) AS pdp_pd_point");
			sql.append(" FROM");
			sql.append(" t_base_product t");
			sql.append(" LEFT JOIN t_base_product_shop_price pdp ON pdp.pdp_pd_code = t.pd_code AND pdp.companyid = t.companyid AND pdp.pdp_shop_code IN (").append(shopcodes).append(")");
			sql.append(" WHERE");
			sql.append(" 1 = 1");
			sql.append(" AND t.pd_code in (").append(pdcodes).append(")");
			sql.append(" AND t.pd_state !='2'");
			sql.append(" AND t.companyid = :companyid");
			shopPriceList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Product_Shop_Price.class));
			
			T_Base_Product_Shop_Price shop_price = null;
			if(shopPriceList != null && shopPriceList.size()>0){
				for(int i=0;i<shopPriceList.size();i++){
					shop_price = shopPriceList.get(i);
					if(shop_price.getPdp_shop_code() !=null && !"".equals(shop_price.getPdp_shop_code())){
						shopPriceMap.put(shop_price.getPdp_shop_code()+"_"+shop_price.getPd_code(), shop_price);
					}
				}
			}
			
			//把选定的货号，对应的分店价格，放到resultList中，如果选定的货号没有分店价格，就选取商品资料中该货号对应的价格
			List<T_Shop_PriceList> resultList = new ArrayList<T_Shop_PriceList>();
			T_Shop_PriceList model = null;
			for (String shopCode : addShopCode) {
				for (String pdCode : pdCodeList) {
					if(shopPriceMap.containsKey(shopCode+"_"+pdCode)){
						shop_price = shopPriceMap.get(shopCode+"_"+pdCode);
						model = new T_Shop_PriceList();
						model.setSpl_old_sellprice(shop_price.getPdp_sell_price());
						model.setSpl_new_sellprice(shop_price.getPdp_sell_price()*discount);
						model.setSpl_old_vipprice(shop_price.getPdp_vip_price());
						model.setSpl_new_vipprice(shop_price.getPdp_vip_price());
						model.setSpl_old_sortprice(shop_price.getPdp_sort_price());
						model.setSpl_new_sortprice(shop_price.getPdp_sort_price());
						model.setSpl_old_costprice(shop_price.getPdp_cost_price());
						model.setSpl_new_costprice(shop_price.getPdp_cost_price());
						model.setSpl_pd_point(shop_price.getPdp_pd_point());
						model.setSpl_pd_code(pdCode);
						model.setSpl_shop_code(shopCode);
						model.setSpl_us_id(spl_us_id);
						model.setCompanyid(companyid);
					} else {
						product = productMap.get(pdCode);
						model = new T_Shop_PriceList();
						model.setSpl_old_sellprice(product.getPd_sell_price());
						model.setSpl_new_sellprice(product.getPd_sell_price()*discount);
						model.setSpl_old_vipprice(product.getPd_vip_price());
						model.setSpl_new_vipprice(product.getPd_vip_price());
						model.setSpl_old_sortprice(product.getPd_sort_price());
						model.setSpl_new_sortprice(product.getPd_sort_price());
						if(CommonUtil.FOUR.equals(sp_shop_type) || CommonUtil.FIVE.equals(sp_shop_type)){//加盟店或者合伙店将总部的配送价保存在分店的成本价中
							model.setSpl_old_costprice(product.getPd_sort_price());
							model.setSpl_new_costprice(product.getPd_sort_price());
						}else{
							model.setSpl_old_costprice(product.getPd_cost_price());
							model.setSpl_new_costprice(product.getPd_cost_price());
						}
						model.setSpl_pd_point(product.getPd_point());
						model.setSpl_pd_code(pdCode);
						model.setSpl_shop_code(shopCode);
						model.setSpl_us_id(spl_us_id);
						model.setCompanyid(companyid);
					}
					resultList.add(model);
				}
			}
			
			//把选定的货号，添加到商品调价临时表中
			sql.setLength(0);
			sql.append(" insert into t_shop_pricelist_temp ");
			sql.append(" (spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,");
			sql.append(" spl_new_sortprice,spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,companyid)");
			sql.append(" VALUES ");
			sql.append(" (:spl_pd_code,:spl_pd_point,:spl_old_sellprice,:spl_new_sellprice,:spl_old_vipprice,:spl_new_vipprice,:spl_old_sortprice,");
			sql.append(" :spl_new_sortprice,:spl_old_costprice,:spl_new_costprice,:spl_shop_code,:spl_us_id,:companyid)");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(resultList.toArray()));
		}
	}

	@Override
	public void update_templist_byPdCode(Map<String, Object> params) {
		Integer spl_us_id = (Integer)params.get("spl_us_id");
		String spl_pd_code = (String)params.get("spl_pd_code");
		Integer companyid = (Integer)params.get("companyid");
		Double unitPrice = (Double)params.get("unitPrice");
		T_Shop_PriceList priceList = new T_Shop_PriceList();
		priceList.setSpl_us_id(spl_us_id);
		priceList.setSpl_pd_code(spl_pd_code);
		priceList.setCompanyid(companyid);
		priceList.setSpl_new_sellprice(unitPrice);
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_pricelist_temp SET ");
		sql.append(" spl_new_sellprice=:spl_new_sellprice ");
		sql.append(" WHERE spl_us_id = :spl_us_id");
		sql.append(" AND companyid = :companyid"); 
		if(!StringUtil.trimString(spl_pd_code).equals("")){
			sql.append(" AND spl_pd_code = :spl_pd_code");
		}
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(priceList));
	}

	@Override
	public void savePrice_templist(Map<String, Object> params) {
		Object prices = params.get("prices");
		Object price_type = params.get("price_type");
		Integer spl_us_id = (Integer)params.get("spl_us_id");
		String spl_pd_code = (String)params.get("spl_pd_code");
		Integer companyid = (Integer)params.get("companyid");
		Object spl_shop_code = params.get("spl_shop_code");
		T_Shop_PriceList priceList = new T_Shop_PriceList();
		priceList.setSpl_us_id(spl_us_id);
		priceList.setSpl_pd_code(spl_pd_code);
		priceList.setCompanyid(companyid);
		
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_pricelist_temp SET ");
		sql.append(price_type).append(" = ").append(prices);
		sql.append(" WHERE 1=1 ");
		sql.append(" AND spl_pd_code = :spl_pd_code");
		if(!StringUtil.trimString(spl_shop_code).equals("")){
			sql.append(" AND spl_shop_code = '"+spl_shop_code+"'"); 
		}
		sql.append(" AND spl_us_id = :spl_us_id");
		sql.append(" AND companyid = :companyid"); 
		if(!StringUtil.trimString(spl_pd_code).equals("")){
			sql.append(" AND spl_pd_code = :spl_pd_code");
		}
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(priceList));
	}

	@Override
	public void savePoint_templist(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_pricelist_temp SET ");
		sql.append("spl_pd_point=:spl_pd_point");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND spl_id = :spl_id");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public void save(T_Shop_Price shop_Price) {
		String prefix = CommonUtil.NUMBER_PREFIX_PRICE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(sp_number))) AS new_number");
		sql.append(" FROM t_shop_price");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(sp_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", shop_Price.getCompanyid()), String.class);
		shop_Price.setSp_number(new_number);
		
		String shop_code = shop_Price.getSp_shop_code();
		String[] shop_codes = null; 
		if(shop_code !=null && !"".equals(shop_code)){
			shop_codes = shop_code.split(",");
		}
		//添加调价店铺
		List<T_Shop_Price_Shop> priceShopList = new ArrayList<T_Shop_Price_Shop>();
		T_Shop_Price_Shop t_Shop_Price_Shop = null;
		for(int i=0;i<shop_codes.length;i++){
			t_Shop_Price_Shop = new T_Shop_Price_Shop();
			t_Shop_Price_Shop.setSps_number(shop_Price.getSp_number());
			t_Shop_Price_Shop.setSps_shop_code(shop_codes[i]);
			t_Shop_Price_Shop.setCompanyid(shop_Price.getCompanyid());
			priceShopList.add(t_Shop_Price_Shop);
		}
		sql.setLength(0);
		sql.append(" insert into t_shop_price_shop ");
		sql.append(" (sps_number,sps_shop_code,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:sps_number,:sps_shop_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(priceShopList.toArray()));
		
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_price");
		sql.append(" (sp_number,sp_maker,sp_makerdate,sp_sysdate,sp_manager,sp_is_sellprice,sp_is_vipprice,sp_is_sortprice,sp_is_costprice,sp_state,");
		sql.append(" sp_shop_type,sp_us_id,sp_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sp_number,:sp_maker,:sp_makerdate,:sp_sysdate,:sp_manager,:sp_is_sellprice,:sp_is_vipprice,:sp_is_sortprice,:sp_is_costprice,:sp_state,");
		sql.append(" :sp_shop_type,:sp_us_id,:sp_remark,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shop_Price),holder);
		shop_Price.setSp_id(holder.getKey().intValue());
	}

	@Override
	public void update(T_Shop_Price shop_Price) {
		//删除调价店铺表
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_price_shop");
		sql.append(" WHERE sps_number=:sps_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sps_number", shop_Price.getSp_number()).addValue("companyid", shop_Price.getCompanyid()));
		
		String shop_code = shop_Price.getSp_shop_code();
		String[] shop_codes = null; 
		if(shop_code !=null && !"".equals(shop_code)){
			shop_codes = shop_code.split(",");
		}
		//添加调价店铺
		List<T_Shop_Price_Shop> priceShopList = new ArrayList<T_Shop_Price_Shop>();
		T_Shop_Price_Shop t_Shop_Price_Shop = null;
		for(int i=0;i<shop_codes.length;i++){
			t_Shop_Price_Shop = new T_Shop_Price_Shop();
			t_Shop_Price_Shop.setSps_number(shop_Price.getSp_number());
			t_Shop_Price_Shop.setSps_shop_code(shop_codes[i]);
			t_Shop_Price_Shop.setCompanyid(shop_Price.getCompanyid());
			priceShopList.add(t_Shop_Price_Shop);
		}
		sql.setLength(0);
		sql.append(" insert into t_shop_price_shop ");
		sql.append(" (sps_number,sps_shop_code,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:sps_number,:sps_shop_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(priceShopList.toArray()));
		
		sql.setLength(0);
		sql.append(" UPDATE t_shop_price");
		sql.append(" SET sp_maker=:sp_maker");
		sql.append(" ,sp_makerdate=:sp_makerdate");
		sql.append(" ,sp_manager=:sp_manager");
		sql.append(" ,sp_is_sellprice=:sp_is_sellprice");
		sql.append(" ,sp_is_vipprice=:sp_is_vipprice");
		sql.append(" ,sp_is_sortprice=:sp_is_sortprice");
		sql.append(" ,sp_is_costprice=:sp_is_costprice");
		sql.append(" ,sp_state=:sp_state");
		sql.append(" ,sp_shop_type=:sp_shop_type");
		sql.append(" ,sp_us_id=:sp_us_id");
		sql.append(" ,sp_remark=:sp_remark");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shop_Price));
	}

	@Override
	public void saveList(List<T_Shop_PriceList> shop_PriceLists) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_pricelist");
		sql.append(" (spl_number,spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,");
		sql.append(" spl_new_sortprice,spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:spl_number,:spl_pd_code,:spl_pd_point,:spl_old_sellprice,:spl_new_sellprice,:spl_old_vipprice,:spl_new_vipprice,:spl_old_sortprice,");
		sql.append(" :spl_new_sortprice,:spl_old_costprice,:spl_new_costprice,:spl_shop_code,:spl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(shop_PriceLists.toArray()));
	}
	
	@Override
	public void save(T_Shop_Price shop_Price,List<T_Shop_PriceList> shop_PriceLists) {
		String prefix = CommonUtil.NUMBER_PREFIX_PRICE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(sp_number))) AS new_number");
		sql.append(" FROM t_shop_price");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(sp_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", shop_Price.getCompanyid()), String.class);
		shop_Price.setSp_number(new_number);
		
		String shop_code = shop_Price.getSp_shop_code();
		String[] shop_codes = null; 
		if(shop_code !=null && !"".equals(shop_code)){
			shop_codes = shop_code.split(",");
		}
		//添加调价店铺
		List<T_Shop_Price_Shop> priceShopList = new ArrayList<T_Shop_Price_Shop>();
		T_Shop_Price_Shop t_Shop_Price_Shop = null;
		for(int i=0;i<shop_codes.length;i++){
			t_Shop_Price_Shop = new T_Shop_Price_Shop();
			t_Shop_Price_Shop.setSps_number(shop_Price.getSp_number());
			t_Shop_Price_Shop.setSps_shop_code(shop_codes[i]);
			t_Shop_Price_Shop.setCompanyid(shop_Price.getCompanyid());
			priceShopList.add(t_Shop_Price_Shop);
		}
		sql.setLength(0);
		sql.append(" insert into t_shop_price_shop ");
		sql.append(" (sps_number,sps_shop_code,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:sps_number,:sps_shop_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(priceShopList.toArray()));
		
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_price");
		sql.append(" (sp_number,sp_maker,sp_makerdate,sp_sysdate,sp_manager,sp_is_sellprice,sp_is_vipprice,sp_is_sortprice,sp_is_costprice,sp_state,");
		sql.append(" sp_shop_type,sp_us_id,sp_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:sp_number,:sp_maker,:sp_makerdate,:sp_sysdate,:sp_manager,:sp_is_sellprice,:sp_is_vipprice,:sp_is_sortprice,:sp_is_costprice,:sp_state,");
		sql.append(" :sp_shop_type,:sp_us_id,:sp_remark,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shop_Price),holder);
		shop_Price.setSp_id(holder.getKey().intValue());
		
		for (T_Shop_PriceList temp : shop_PriceLists) {
			temp.setSpl_number(shop_Price.getSp_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_shop_pricelist");
		sql.append(" (spl_number,spl_pd_code,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,");
		sql.append(" spl_new_sortprice,spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:spl_number,:spl_pd_code,:spl_old_sellprice,:spl_new_sellprice,:spl_old_vipprice,:spl_new_vipprice,:spl_old_sortprice,");
		sql.append(" :spl_new_sortprice,:spl_old_costprice,:spl_new_costprice,:spl_shop_code,:spl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(shop_PriceLists.toArray()));
	}

	@Override
	public void temp_clear(Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_pricelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND spl_us_id = :spl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("spl_us_id", us_id).addValue("companyid", companyid));
	}

	@Override
	public void temp_del(Integer spl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_pricelist_temp");
		sql.append(" WHERE spl_id=:spl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("spl_id", spl_id));
	}

	@Override
	public T_Shop_Price load(Integer sp_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT t.sp_id,t.sp_number,t.sp_maker,t.sp_makerdate,t.sp_sysdate,t.sp_manager,t.sp_is_sellprice,t.sp_is_vipprice,t.sp_is_sortprice,t.sp_is_costprice,");
		sql.append(" t.sp_state,t.sp_shop_type,t.sp_us_id,t.sp_remark,t.companyid,GROUP_CONCAT(sp.sp_name) AS sp_sp_name,GROUP_CONCAT(sp.sp_code) AS sp_shop_code ");
		sql.append(" FROM t_shop_price t");
		sql.append(" join t_shop_price_shop sps on sps.sps_number = t.sp_number and sps.companyid = t.companyid ");
		sql.append(" join t_base_shop sp on sp.sp_code = sps.sps_shop_code AND sp.companyid = sps.companyid ");
		sql.append(" WHERE t.sp_id = :sp_id");
		sql.append(" group by t.sp_number ");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("sp_id", sp_id),
					new BeanPropertyRowMapper<>(T_Shop_Price.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_Price check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT t.sp_id,t.sp_number,t.sp_is_sellprice,t.sp_is_vipprice,t.sp_is_sortprice,t.sp_is_costprice,");
		sql.append(" t.sp_state,t.sp_us_id,t.companyid");
		sql.append(" FROM t_shop_price t");
		sql.append(" WHERE t.sp_number = :sp_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_Price.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void updateApprove(T_Shop_Price price) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_price");
		sql.append(" SET sp_state=:sp_state");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(price));
	}

	@Override
	public void update_base_shopprice(T_Shop_Price price) {
		//修改价格标记
		Integer IsSellPrice = price.getSp_is_sellprice();
		Integer IsVipPrice = price.getSp_is_vipprice();
		Integer IsSortPrice = price.getSp_is_sortprice();
		Integer IsCostPrice = price.getSp_is_costprice();
		
		//遍历分店
		//判断选择需要修改哪些价格
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_base_product_shop_price as pdp,t_shop_pricelist as spl set ");
		sql.append(" pdp_pd_point = spl.spl_pd_point,");
		if(IsSellPrice == 1){sql.append(" pdp.pdp_sell_price = spl.spl_new_sellprice,");}
		if(IsVipPrice == 1){sql.append(" pdp.pdp_vip_price = spl.spl_new_vipprice,");}
		if(IsSortPrice == 1){sql.append(" pdp.pdp_sort_price = spl.spl_new_sortprice,");}
		if(IsCostPrice == 1){sql.append(" pdp.pdp_cost_price = spl.spl_new_costprice,");}
		sql.setCharAt(sql.length()-1,' ');//去掉最后一个逗号
		sql.append(" where 1=1");
		sql.append(" and pdp.companyid=spl.companyid");
		sql.append(" and spl.companyid = :companyid");
		sql.append(" and pdp.companyid = :companyid");
		sql.append(" and spl.spl_number = :sp_number ");
		sql.append(" and pdp.pdp_pd_code=spl.spl_pd_code");
		sql.append(" and pdp.pdp_shop_code=spl.spl_shop_code");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(price));
		//没有就插入
		sql.setLength(0);
		sql.append(" insert into t_base_product_shop_price(pdp_pd_code,pdp_shop_code,pdp_pd_point,pdp_cost_price,pdp_sell_price,pdp_vip_price,pdp_sort_price,companyid)");
		sql.append(" (select spl_pd_code,spl_shop_code,spl_pd_point,spl_new_costprice,spl_new_sellprice,spl_new_vipprice,spl_new_sortprice,spl.companyid");
		sql.append(" from t_shop_pricelist as spl");
		sql.append(" left join (select pdp_pd_code,pdp_shop_code,companyid from t_base_product_shop_price) as pdp");
		sql.append(" on spl.spl_pd_code=pdp.pdp_pd_code and spl.companyid = pdp.companyid  and spl.spl_shop_code=pdp.pdp_shop_code");
		sql.append(" where spl.spl_number=:sp_number");
		sql.append(" and pdp_shop_code IS NULL");
		sql.append(" and spl.companyid=:companyid");
		sql.append(" )");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(price));
	}

	@Override
	public void del(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_price");
		sql.append(" WHERE sp_number=:sp_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sp_number", number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_shop_price_shop");
		sql.append(" WHERE sps_number=:sps_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sps_number", number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_shop_pricelist");
		sql.append(" WHERE spl_number=:spl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("spl_number", number).addValue("companyid", companyid));
	}

	@Override
	public List<T_Shop_PriceList> detail_list_forsavetemp(String sp_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT spl_id,spl_number,spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,spl_new_sortprice,");
		sql.append(" spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,t.companyid ");
		sql.append(" FROM t_shop_pricelist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND spl_number = :spl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY spl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("spl_number", sp_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_PriceList.class));
	}

	@Override
	public void temp_save(List<T_Shop_PriceList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_pricelist_temp");
		sql.append(" (spl_pd_code,spl_pd_point,spl_old_sellprice,spl_new_sellprice,spl_old_vipprice,spl_new_vipprice,spl_old_sortprice,spl_new_sortprice,");
		sql.append(" spl_old_costprice,spl_new_costprice,spl_shop_code,spl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:spl_pd_code,:spl_pd_point,:spl_old_sellprice,:spl_new_sellprice,:spl_old_vipprice,:spl_new_vipprice,:spl_old_sortprice,:spl_new_sortprice,");
		sql.append(" :spl_old_costprice,:spl_new_costprice,:spl_shop_code,:spl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void deleteList(String sp_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_pricelist");
		sql.append(" WHERE spl_number=:spl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("spl_number", sp_number).addValue("companyid", companyid));
	}
}
